package com.cloudcc.implementationTools;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.cloudcc.ccTool.CCObject;
import com.cloudcc.ccTool.CCService;
import com.cloudcc.ccTool.UserInfo;
import org.apache.poi.ss.usermodel.*;

import java.io.*;
import java.net.HttpURLConnection;
import java.net.URL;
import java.util.List;
import java.util.Map;
import java.util.PropertyResourceBundle;
import java.util.ResourceBundle;

import static cn.hutool.poi.excel.cell.CellUtil.getCellValue;
import static java.lang.Thread.sleep;

public class ExcelReader {

    private CCService cs;
    private UserInfo userInfo;

    public ExcelReader(UserInfo userInfo) {
        this.userInfo = userInfo;
        cs = new CCService(userInfo);
    }
    ResourceBundle prb = PropertyResourceBundle.getBundle("application");// 获取配置文件;
    public String PATH = prb.getString("gatewayUrl");
    private static final String ADDRESS = "E:\\桌面\\importobject.xlsx";

    public static void main(String[] args) throws Exception {
        new ExcelReader(new UserInfo()).insertcco();
    }

    public void insertcco() throws Exception {
        File realfile = new File(ADDRESS);
        Workbook wb = WorkbookFactory.create(realfile);
        //查询有多少个sheet页
        int sheetNum = wb.getNumberOfSheets();
        System.out.println("sheetNum = " + sheetNum);
        JSONArray objArray = new JSONArray();
        System.out.println("创建对象开始 ============ ");
        JSONObject creatObjectpar = new JSONObject();//创建对象参数
        creatObjectpar.put("iscreatperm", "true");
        creatObjectpar.put("profileFieldArr", createProfileFieldJson("", "object"));
        for (int i = 0; i < sheetNum; i++) {
            Sheet sheet1 = wb.getSheetAt(i);
            int count = 0;
            String objectapiname = "";
            JSONObject objJson = new JSONObject();
            for (Row row : sheet1) {
                count++;
                if (count == 1) {
                    String objparam = getStringCellValue(row, 0);
                    JSONObject ccobject = JSONObject.parseObject(objparam);
                    objectapiname = ccobject.getString("objectapiname");
                    String nameLabel = ccobject.getString("nameLabel");
                    creatObjectpar.put("nameLabel", nameLabel);
                    ccobject.put("schemetableName", objectapiname);
                    ccobject.remove("objectapiname");
                    ccobject.remove("nameLabel");
                    creatObjectpar.put("obj", ccobject);
                    objJson.put("objname", objectapiname);
                }
            }
            String objectCheckSql = String.format("select id from tp_sys_object where schemetable_Name='%s'", objectapiname);
            List<CCObject> objectCheck = cs.cqlQuery("tp_sys_object", objectCheckSql);
            String objectid = "";
            if (!objectCheck.isEmpty()) {
                objectid = objectCheck.get(0).get("id").toString();
                objJson.put("objid", objectid);
                System.out.println("对象api名称'" + objectapiname + "'已存在，请更改api名称");
            } else {

                System.out.println(" creatObjectpar.toJSONString() = " +  creatObjectpar.toJSONString());
                String response = cs.CCPost(PATH + "/setup/api/customObject/saveButton", creatObjectpar.toString());
                JSONObject resp = JSONObject.parseObject(response);
                if (resp.getString("result").equals("true")) {
                    objectid = resp.getJSONObject("data").getString("id");
                }
                objJson.put("objid", objectid);
                System.out.println("response = " + response);
            }
            objArray.add(objJson);
        }
        System.out.println("objArray = " + objArray);
        System.out.println("创建对象结束 ============ ");
        System.out.println("字段创建开始 ============ ");
        for (int i = 0; i < sheetNum; i++) {
            String objid = objArray.getJSONObject(i).getString("objid");
            String objname = objArray.getJSONObject(i).getString("objname");
            Sheet sheet1 = wb.getSheetAt(i);
            int count = 0;
            for (Row row : sheet1) {
                count++;
                if (count == 1 || count == 2) {
                    continue;
                }
                sleep(10000);
                String bq = getStringCellValue(row, 0);
                String api = getStringCellValue(row, 1);
                String objSql=String.format("select id from  TP_SYS_SCHEMETABLE where schemetable_id='%s' and schemefield_name='%s'",objid,api);
                List<CCObject> ccObjects = cs.cqlQuery("TP_SYS_SCHEMETABLE", objSql);
                if (!ccObjects.isEmpty()) {
                    System.out.println(String.format("对象'%s'下已存在字段'%s'", objname, api));
                    continue;
                }
                String type = getStringCellValue(row, 2);
                String lx = getTypeCode(type);
                String lxx = lx + ":" + type;
                JSONObject ret = new JSONObject();
                handleSpecialTypes(row, lx, api, ret);
                ret.put("objid", objid);
                ret.put("dtype", lxx);
                ret.put("fdtype", lx);
                if (!lx.equals("Z")&&!lx.equals("V")) {
                    ret.put("ischecked", "false");
                    ret.put("iscover", "");
                }

                handleFormulaType(row, lx, ret);
                ret.put("layoutIds", "");

                JSONObject obj = createObjectJson(bq, api, lx, row);
                if (obj == null) {
                    continue;
                }
                ret.put("obj", obj);
                JSONArray objects = createProfileFieldJson(lx, "field");
                ret.put("profileFieldJson", objects);
                System.out.println("ret = " + ret);
                String response = cs.CCPost(PATH + "/setup/api/fieldSetup/save", ret.toJSONString());
//                String response = postWithToken(PATH + "/setup/api/fieldSetup/save", ret.toJSONString(), cs.getToken());
                System.out.println(response);
            }
        }
        System.out.println("字段创建结束 ============ ");
    }

    private String getStringCellValue(Row row, int cellIndex) {
        Cell cell = row.getCell(cellIndex);
        return getCellValue(cell) == null ? "" : getCellValue(cell).toString().trim();
    }
    private void handleSpecialTypes(Row row, String lx, String api, JSONObject ret) throws Exception {
        if ("L".equals(lx) || "Q".equals(lx)) {
            List<Map> picklistValue = cs.getPicklistValue("quotedetail", api, "");
            String val = "";
            if (picklistValue.size() > 0) {
                val = getPicklistValues(picklistValue);
            } else {
                String lbz = getStringCellValue(row, 3);
                String[] split = lbz.split("/");
                for (String s : split) {
                    val += s + "\r\n";
                }
            }
            ret.put("useGlobalSelect", "0");
            ret.put("globalSelectId", "province");
            ret.put("ptext", val);
            ret.put("isPicklistSorted", "0");
            ret.put("defPl", "0");
        } else if ("encd".equals(lx)) {
            ret.put("masktype", "all");
            ret.put("maskcharacter", "*");
            ret.put("encrypttype", "{AAAA}{****}{AAAA}");
        }else if ("V".equals(lx)) {
            ret.put("showFormat",getStringCellValue(row, 10)==""?"{yyyy}{mm}{dd}{000}":getStringCellValue(row, 10));
            ret.put("beginIndex", getStringCellValue(row, 11)==""?"0":getStringCellValue(row, 11));
        }
    }

    private String getPicklistValues(List<Map> picklistValue) {
        StringBuilder val = new StringBuilder();
        for (Map map : picklistValue) {
            String s = map.get("key") == null ? "" : map.get("key").toString();
            val.append(s).append("\r\n");
        }
        return val.toString();
    }

    private void handleFormulaType(Row row, String lx, JSONObject ret) {
        if ("Z".equals(lx)) {
            ret.put("formulaText", getStringCellValue(row, 6));
            ret.put("formulaType", getTypeCode(getStringCellValue(row, 7)));
        } else  {
            if (!"V".equals(lx)){
                ret.put("formulaText", "");
            }

        }
    }

    private JSONObject createObjectJson(String bq, String api, String lx, Row row) throws Exception {
        JSONObject obj = new JSONObject();
        obj.put("id", "");
        obj.put("nameLabel", bq);
        obj.put("apiname", api);
        obj.put("remark", "");
        obj.put("helps", "");

        if ("Y".equals(lx) || "M".equals(lx) || "MR".equals(lx)) {
            String lookupObj = getStringCellValue(row, 4);
            //判断首字母是否大写，创建一个新的字符串  如果大写改成小写，如果小写改成大写
            String first = lookupObj.substring(0, 1);
            String lookupObj_new = "";
            if (first.equals(first.toUpperCase())) {
                lookupObj_new = first.toLowerCase() + lookupObj.substring(1, lookupObj.length());
            } else {
                lookupObj_new = first.toUpperCase() + lookupObj.substring(1, lookupObj.length());
            }
            String objid_sql = "select id from TP_SYS_OBJECT where schemetable_name='" + lookupObj + "' or schemetable_name='" + lookupObj_new + "'";
            List<CCObject> tpSysObject = cs.cqlQuery("TP_SYS_OBJECT", objid_sql);
            if (!tpSysObject.isEmpty()) {
                obj.put("lookupObj", tpSysObject.get(0).get("id").toString());
            } else {
                System.out.println(bq + "未找到");
                return null;
            }
            if(!"MR".equals(lx)){
                obj.put("childrelationName", getStringCellValue(row, 5));
                obj.put("defaultValue", "");
            }

            if ("Y".equals(lx)) {
                obj.put("lookupObjDefaultField", "--无--");
            }
            if ("M".equals(lx)) {
                obj.put("mainlayoutIds", "");
            }
        }
        String schemefieldLength = getStringCellValue(row, 8);
        if ("".equals(schemefieldLength) || schemefieldLength == null) {
            schemefieldLength = "255";
        }
        obj.put("schemefieldLength", schemefieldLength);
        if ("N".equals(lx) || "c".equals(lx) || "P".equals(lx) || "Z".equals(lx)) {//带小数位
            if ("N".equals(lx)) {
                obj.put("isrepeat", "true");
                obj.put("displayThousands", "0");
            }
            String decimalPlaces = getStringCellValue(row, 9);
            if ("".equals(decimalPlaces) || decimalPlaces == null) {
                decimalPlaces = "2";
            }
            if ("".equals(schemefieldLength) || schemefieldLength == null) {
                schemefieldLength = "10";
            }
            obj.put("schemefieldLength", schemefieldLength);
            obj.put("decimalPlaces", decimalPlaces);
        }
        if ("S".equals(lx)) {
            obj.put("isrepeat", "true");
            obj.put("casesensitive", "");
            obj.put("placeholder", "");
        }
        if ("F".equals(lx)) {
            obj.put("defaultValue", "");
            obj.put("isrepeat", "3");
        }
        if ("L".equals(lx)) {
            obj.put("edittype", "select");
        }

        if ("Q".equals(lx)) {
            obj.put("visibleLines", "4");
            obj.put("showalloptions", "0");
            obj.put("renderstyle", "1");
        }

        return obj;
    }

    private JSONArray createProfileFieldJson(String lx, String usein) throws Exception {
        JSONArray objects = new JSONArray();
        List<CCObject> profiles = cs.cqlQuery("TP_SYS_PROFILE", "select id from tp_sys_profile");
        for (CCObject proCCO : profiles) {
            JSONObject profile = new JSONObject();
            if (usein.equals("object")) {
                profile.put("profileid", proCCO.get("id").toString());
                if (proCCO.get("id").toString().equals("aaa000001")) {
                    profile.put("permission", "1,1,1,1,1,1");
                } else {
                    profile.put("permission", "1,1,1,1,1,0");
                }


            } else if (usein.equals("field")) {
                profile.put("profileId", proCCO.get("id").toString());
                profile.put("visible", "true");
                profile.put("readonly", "false");
                if ("encd".equals(lx)) {
                    profile.put("encryptvisible", "false");
                }
            }

            objects.add(profile);
        }
        return objects;
    }

    public String getTypeCode(String type) {
        switch (type) {
            case "选项列表":
                return "L";
            case "日期":
                return "D";
            case "币种":
                return "c";
            case "查找关系":
                return "Y";
            case "查找多选":
                return "MR";
            case "主详信息关系":
                return "M";
            case "公式":
                return "Z";
            case "数字":
                return "N";
            case "百分比":
                return "P";
            case "地址":
                return "AD";
            case "电话":
                return "H";
            case "加密文本(显示加密)":
                return "encd";
            case "加密文本（显示加密）":
                return "encd";
            case "文本区":
                return "X";
            case "文本区（长）":
                return "J";
            case "文本区(长)":
                return "J";
            case "选项列表（多选）":
                return "Q";
            case "选项列表(多选)":
                return "Q";
            case "文本":
                return "S";
            case "文件":
                return "FL";
            case "复选框":
                return "B";
            case "电子邮件":
                return "E";
            case "日期/时间":
                return "F";
            case "自动编号":
                return "V";
            default:
                return "";
        }
    }
}
